package ch.sv7.tool.dbmanager.db.sql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import ch.sv7.tool.dbmanager.DbManagerException;
import ch.sv7.tool.dbmanager.db.ColumnSchema;
import ch.sv7.tool.dbmanager.db.ColumnTypeManager;
import ch.sv7.tool.dbmanager.db.Field;
import ch.sv7.tool.dbmanager.db.Row;
import ch.sv7.tool.dbmanager.db.Table;
import ch.sv7.tool.dbmanager.db.TableReader;
import ch.sv7.tool.dbmanager.db.TableSchema;
import ch.sv7.tool.dbmanager.db.TableWriter;

public class SQLFullTableReader implements TableReader {

	private Connection con;

	private int nbRow;

	public SQLFullTableReader(Connection con) {
		nbRow = 0;
		this.con = con;
		nbRow = 0;
	}

	public Table readTable(TableSchema schema) throws DbManagerException {
		Table table = new Table(schema.getName());
		traverseTable(null, table, schema);
		return table;
	}

	public void transferTable(TableSchema schema, TableWriter writer) throws DbManagerException {
		writer.writeHeader(schema);
		traverseTable(writer, null, schema);
		writer.writeFooter(schema);
	}

	private void traverseTable(TableWriter writer, Table table, TableSchema schema) throws DbManagerException {
		String sql = "SELECT ";
		for (int i = 0; i < schema.getColumnSchemas().length; i++) {
			if (i != 0)
				sql = sql + ", ";
			sql = sql + schema.getColumnSchemas()[i].getName();
		}

		sql = sql + " FROM " + schema.getFullName();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = con.createStatement();
			for (rs = stmt.executeQuery(sql); rs.next();) {
				Row row = new Row();
				for (int i = 0; i < schema.getColumnSchemas().length; i++) {
					ColumnSchema column = schema.getColumnSchemas()[i];
					String fieldName = column.getName();
					ch.sv7.tool.dbmanager.db.ColumnType fieldType = column.getType();
					Field field = new Field(fieldName, ColumnTypeManager.readFromDatabase(rs, i + 1, fieldType));
					row.addField(field);
				}

				nbRow++;
				if (table != null)
					table.add(row);
				if (writer != null)
					writer.writeRow(row, schema);
			}

			System.out.println("Nb. of rows read from DB : " + nbRow);
			nbRow = 0;
		} catch (SQLException e) {
			throw new DbManagerException("Unable to read table " + schema.getName(), e);
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}
		}
	}
}
